package edu.rit.varapp.datalayer;

import edu.rit.varapp.db.DatabaseConnection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * CustomerOps provides methods to perform various functions on the database in relation to Customer objects.
 */
public class CustomerOps {

    private final ModuleOps mOps = new ModuleOps();
    private DatabaseConnection conn;

    /**
     * Default constructor.
     * @param conn
     */
    public CustomerOps() {
        this.conn = DatabaseConnection.instance();
    }

    /**
     * Adds a new customer to the database.
     * @param customer The customer information to add to the database.
     * @return True if the insert was successful, else false.
     * @throws SQLException
     */
    public synchronized boolean insertCustomer( Customer customer ) throws SQLException {
        String sql = "INSERT INTO customers (" + Customer.columnsToSQL() + ") "
                     + "VALUES (" + customer.valuesToSQL( true ) + ")";
        int val = conn.executeUpdateQuery( sql );
        if( val > 0 ) {
            return true;
        }
        return false;
    }

    /**
     * Deletes a customer from the database. This will first delete all modules associated with this customer.
     * @param customer The customer information to delete.
     * @return True if the deletion was successful, else false.
     * @throws SQLException
     */
    public boolean deleteCustomer( Customer customer ) throws SQLException {
        //First we need to delete any associated modules with this customer
        for( Module module : mOps.retrieveModules( customer ) ) {
            mOps.deleteModule( module );
        }
        String sql = "DELETE FROM customers WHERE EntityId = " + customer.getEntityId();
        int val = conn.executeUpdateQuery( sql );
        if( val > 0 ) {
            return true;
        }
        return false;
    }

    /**
     * Updates a customer's information. This method will first delete the existing customer, and then insert the
     * "new" customer. Since we are deleting a customer and then inserting, we need to use a transaction in case
     * the delete fails.
     * @param garbage Since this method might not be used and has the same signature as the actual updateCustomer,
     *                we added a garbage field for the method, allowing the caller to specify which method they want.
     * @param customer The customer information to update an existing customer.
     * @return True if the update was successful, else false.
     * @throws SQLException
     */
    public synchronized boolean updateCustomer( Customer customer, String garbage ) throws SQLException {
        try {
            conn.setAutoCommit( false );
            String password = getPassword( customer.getEntityId() );
            if( deleteCustomer( customer ) ) {
                if( insertCustomer( customer ) ) {
                    reinsertPassword( customer.getEntityId(), password );
                    conn.setAutoCommit( true );
                    return true;
                }
            }
        } catch( SQLException sqle ) {
            conn.rollback();
            throw sqle;
        }
        return false;
    }

    /**
     * Updates an existing customer's information.
     * @param customer The customer information to update an existing customer.
     * @return True if the update was successful, else false.
     * @throws SQLException
     */
    public synchronized boolean updateCustomer( Customer customer, int entityId ) throws SQLException {
        HashMap<String, String> dataMap = customer.asMap( true );
        String sql = "UPDATE customers SET ";
        for( String key : dataMap.keySet() ) {
            sql += key.replaceAll( " ", "" ) + "=\"" + dataMap.get( key ) + "\", ";
        }
        //Trim the last ', '
        sql = sql.substring( 0, sql.length() - 2 );
        sql += " WHERE EntityId=" + entityId;
        int val = conn.executeUpdateQuery( sql );
        if( val > 0 ) {
            return true;
        }
        return false;
    }

    /**
     * Retrieves any existing Customer information from the provided contact name and company name.
     * @return The customer information that has matching contact name and company name, or null if there is no such
     *         customer.
     * @throws SQLException
     */
    public Customer retrieveCustomer( String contactName, String companyName ) throws SQLException {
        //Checks to see if any apostrophes exist and then to replace it with an identifier for the sql statement
        companyName = companyName.replaceAll( "'", "\\\\'" );
        String sql = "SELECT " + Customer.columnsToSQL() + " FROM customers WHERE ContactName like '%" + contactName
                     + "%' AND CompanyName like '%" + companyName + "%'";
        ResultSet rs = conn.executeQuery( sql );
        if( rs.next() ) {
            return buildCustomer( conn.getSingleRow( rs ) );
        }
        return null;
    }

    /**
     * Retrieves all existing Customers with the specified contact name.
     * @return The list of all customer information that has a matching contact name, or an empty list if there are no
     *         such customers.
     * @throws SQLException
     */
    public ArrayList<Customer> retrieveCustomers( String contactName ) throws SQLException {
        ArrayList<Customer> ret = new ArrayList<Customer>();
        String sql = "SELECT " + Customer.columnsToSQL() + " FROM customers WHERE "
                     + "ContactName like '%" + contactName + "%'";
        ResultSet rs = conn.executeQuery( sql );
        ArrayList<ArrayList<String>> temp = conn.getData( rs, false );
        for( ArrayList<String> row : temp ) {
            ret.add( buildCustomer( row ) );
        }
        return ret;
    }

    /**
     * Retrieves existing Customer information from the provided entityId.
     * @return The customer information that has matching entityId, or null if there is no such customer.
     * @throws SQLException
     */
    public Customer retrieveCustomer( int entityId ) throws SQLException {
        String sql = "SELECT " + Customer.columnsToSQL() + " FROM customers WHERE EntityId = " + entityId;
        ResultSet rs = conn.executeQuery( sql );
        if( rs.next() ) {
            return buildCustomer( conn.getSingleRow( rs ) );
        }
        return null;
    }

    /**
     * Returns the last entityId + 1. This entityId will be given to a new Customer as a holder for when the VAR
     * receives the Customer's "real" entityId from the software manufacturer.
     * @return An unused entityId.
     * @throws SQLException
     */
    public synchronized int newEntityId() throws SQLException {
        int ret = -1;
        String sql = "SELECT EntityId FROM customers ORDER BY EntityId DESC LIMIT 0,1";
        ResultSet rs = conn.executeQuery( sql );
        if( rs.next() ) {
            ret = rs.getInt( "EntityId" ) + 1;
        }
        return ret;
    }

    /**
     * When we're updating a customer, we need to retrieve their password because we first delete the whole customer.
     * @param entityId The customer's id to retrieve their hashed password.
     * @return The password as a hashed string. This should never return null.
     * @throws SQLException
     */
    private String getPassword( int entityId ) throws SQLException {
        ResultSet rs = conn.executeQuery( "SELECT pswd FROM customers WHERE EntityId = " + entityId );
        if( rs.next() ) {
            return rs.getString( 1 );
        }
        return null;
    }

    private void reinsertPassword( int entityId, String password ) throws SQLException {
        String sql = "UPDATE customers SET pswd = '" + password + "' WHERE EntityId = " + entityId;
        int success = conn.executeUpdateQuery( sql );
        if( success <= 0 ) {
            throw new SQLException( "Error inserting password (" + password + ") "
                                    + "into customers with entityId: " + entityId );
        }
    }

    /**
     * Builds a Customer object from a row of data.
     * @throws SQLException
     */
    private Customer buildCustomer( ArrayList<String> data ) {
        return new Customer( Integer.parseInt( data.get( 0 ) ),
                             data.get( 1 ),
                             data.get( 2 ),
                             data.get( 3 ),
                             data.get( 4 ),
                             data.get( 5 ),
                             data.get( 6 ),
                             data.get( 7 ),
                             data.get( 8 ),
                             data.get( 9 ) );
    }
}
